"""Lead repository implementation (Synchronous)."""

from __future__ import annotations

from datetime import datetime, timedelta
from typing import Any
from uuid import UUID

from sqlalchemy import func, over
from sqlalchemy.orm import Session, joinedload

from ..models.lead import Lead
from ..models.enums import LeadStage, SourceChannel, QualityGrade
from .base import Repository
from ..schemas.lead import LeadCreate, LeadUpdate


class LeadRepository(Repository[Lead, LeadCreate, LeadUpdate]):
    """Repository for Lead operations (Synchronous)."""

    def __init__(self, session: Session) -> None:
        super().__init__(Lead, session)

    def get_by_owner(
        self, owner_id: int, skip: int = 0, limit: int = 100, include_deleted: bool = False
    ) -> list[Lead]:
        """Get leads by owner with pagination."""
        query = self.session.query(Lead).filter(Lead.owner_id == owner_id)

        if not include_deleted:
            query = query.filter(Lead.is_deleted == False)

        return query.order_by(Lead.created_at.desc()).offset(skip).limit(limit).all()

    def get_with_relations(self, lead_id: int) -> Lead | None:
        """Get lead with all related data."""
        return (
            self.session.query(Lead)
            .filter(Lead.id == lead_id)
            .options(
                joinedload(Lead.contacts),
                joinedload(Lead.lead_notes),  # Changed from notes to lead_notes (relationship)
                joinedload(Lead.ai_summaries),
            )
            .first()
        )

    def get_by_import_batch(self, batch_id: UUID) -> list[Lead]:
        """Get all leads from a specific import batch."""
        return (
            self.session.query(Lead)
            .filter(Lead.import_batch_id == batch_id)
            .order_by(Lead.created_at.desc())
            .all()
        )

    def filter_by_criteria(
        self,
        stage: LeadStage | None = None,
        source_channel: SourceChannel | None = None,
        owner_id: int | None = None,
        skip: int = 0,
        limit: int = 100,
        include_deleted: bool = False,
    ) -> list[Lead]:
        """Filter leads by multiple criteria."""
        query = self.session.query(Lead)

        # Always exclude deleted leads by default
        if not include_deleted:
            query = query.filter(Lead.is_deleted == False)

        if stage is not None:
            query = query.filter(Lead.stage == stage)
        if source_channel is not None:
            query = query.filter(Lead.source_channel == source_channel)
        if owner_id is not None:
            query = query.filter(Lead.owner_id == owner_id)

        return query.order_by(Lead.created_at.desc()).offset(skip).limit(limit).all()

    def count_by_criteria(
        self,
        stage: LeadStage | None = None,
        source_channel: SourceChannel | None = None,
        owner_id: int | None = None,
    ) -> int:
        """Count leads by criteria."""
        query = self.session.query(func.count(Lead.id))

        if stage is not None:
            query = query.filter(Lead.stage == stage)
        if source_channel is not None:
            query = query.filter(Lead.source_channel == source_channel)
        if owner_id is not None:
            query = query.filter(Lead.owner_id == owner_id)

        # fix: 显式转换 scalar() 返回的 Any 为 int，处理 None 情况
        result = query.scalar()
        return int(result) if result is not None else 0

    def get_channel_stats(self, owner_id: int | None = None) -> dict[str, Any]:
        """Get statistics by source channel."""
        query = self.session.query(
            Lead.source_channel, func.count(Lead.id).label("count")
        ).group_by(Lead.source_channel)

        if owner_id is not None:
            query = query.filter(Lead.owner_id == owner_id)

        return {channel.value: count for channel, count in query.all()}

    def get_stage_stats(self, owner_id: int | None = None) -> dict[str, Any]:
        """Get statistics by lead stage."""
        query = self.session.query(Lead.stage, func.count(Lead.id).label("count")).group_by(
            Lead.stage
        )

        if owner_id is not None:
            query = query.filter(Lead.owner_id == owner_id)

        return {stage.value: count for stage, count in query.all()}

    def exists_by_phone(self, phone: str) -> bool:
        """检查手机号是否已存在（去重检测）."""
        count = self.session.query(func.count(Lead.id)).filter(Lead.phone == phone).scalar()
        # fix: 显式转换为 bool，处理 scalar() 返回的 Any
        # 确保即使 count 为 None 也能正确返回 False
        return bool(count) if count is not None else False

    def get_by_phone(self, phone: str) -> Lead | None:
        """根据手机号获取客户（用于智能导入去重）."""
        return (
            self.session.query(Lead)
            .filter(Lead.phone == phone)
            .filter(Lead.is_deleted == False)
            .first()
        )

    def exists_for_owner(self, company: str, owner_id: int) -> bool:
        """旧方法保留兼容性（已废弃）."""
        # 房产CRM不再使用company字段，此方法仅用于向后兼容
        return False

    def count_created_since(self, since: datetime, owner_id: int | None = None) -> int:
        """统计指定时间后创建的客户数量."""
        query = self.session.query(func.count(Lead.id)).filter(Lead.created_at >= since)

        if owner_id is not None:
            query = query.filter(Lead.owner_id == owner_id)

        # fix: 显式转换 scalar() 返回的 Any 为 int
        result = query.scalar()
        return int(result) if result is not None else 0

    def soft_delete(self, lead_id: int, user_id: int) -> bool:
        """软删除客户记录.

        Args:
            lead_id: 客户ID
            user_id: 执行删除的用户ID

        Returns:
            bool: 删除成功返回True，记录不存在返回False
        """
        lead = self.session.query(Lead).filter(Lead.id == lead_id).first()
        if not lead:
            return False

        lead.is_deleted = True
        lead.deleted_at = datetime.utcnow()
        lead.deleted_by = user_id
        self.session.flush()
        return True

    def bulk_soft_delete(self, lead_ids: list[int], user_id: int) -> dict[str, int]:
        """批量软删除客户记录.

        Args:
            lead_ids: 客户ID列表
            user_id: 执行删除的用户ID

        Returns:
            dict: {"success": 成功数, "failed": 失败数, "not_found": 不存在数}
        """
        if not lead_ids:
            return {"success": 0, "failed": 0, "not_found": 0}

        success_count = 0
        not_found_count = 0
        deleted_at = datetime.utcnow()

        for lead_id in lead_ids:
            lead = (
                self.session.query(Lead)
                .filter(
                    Lead.id == lead_id,
                    Lead.is_deleted == False,  # 只删除未删除的记录
                )
                .first()
            )

            if not lead:
                not_found_count += 1
                continue

            lead.is_deleted = True
            lead.deleted_at = deleted_at
            lead.deleted_by = user_id
            success_count += 1

        self.session.flush()

        return {
            "success": success_count,
            "failed": 0,  # 当前实现中没有失败场景
            "not_found": not_found_count,
        }

    def restore(self, lead_id: int) -> bool:
        """恢复软删除的客户记录.

        Args:
            lead_id: 客户ID

        Returns:
            bool: 恢复成功返回True，记录不存在返回False
        """
        lead = self.session.query(Lead).filter(Lead.id == lead_id, Lead.is_deleted).first()

        if not lead:
            return False

        lead.is_deleted = False
        lead.deleted_at = None
        lead.deleted_by = None
        self.session.flush()
        return True

    def bulk_restore(self, lead_ids: list[int]) -> dict[str, int]:
        """批量恢复软删除的客户记录.

        Args:
            lead_ids: 客户ID列表

        Returns:
            dict: {"success": 成功数, "failed": 失败数, "not_found": 不存在数}
        """
        if not lead_ids:
            return {"success": 0, "failed": 0, "not_found": 0}

        success_count = 0
        not_found_count = 0

        for lead_id in lead_ids:
            lead = (
                self.session.query(Lead)
                .filter(
                    Lead.id == lead_id,
                    Lead.is_deleted,  # 只恢复已删除的记录
                )
                .first()
            )

            if not lead:
                not_found_count += 1
                continue

            lead.is_deleted = False
            lead.deleted_at = None
            lead.deleted_by = None
            success_count += 1

        self.session.flush()

        return {"success": success_count, "failed": 0, "not_found": not_found_count}

    def get_deleted_leads(
        self, owner_id: int | None = None, skip: int = 0, limit: int = 100
    ) -> list[Lead]:
        """获取已软删除的客户列表.

        Args:
            owner_id: 负责人ID筛选（可选）
            skip: 跳过记录数
            limit: 返回记录数

        Returns:
            已软删除的客户列表
        """
        query = self.session.query(Lead).filter(Lead.is_deleted)

        if owner_id is not None:
            query = query.filter(Lead.owner_id == owner_id)

        return query.order_by(Lead.deleted_at.desc()).offset(skip).limit(limit).all()

    def filter_by_criteria_with_total(
        self,
        stage: LeadStage | None = None,
        source_channel: SourceChannel | None = None,
        owner_id: int | None = None,
        name: str | None = None,
        phone: str | None = None,
        skip: int = 0,
        limit: int = 100,
        include_deleted: bool = False,
    ) -> tuple[list[Lead], int]:
        """优化的分页查询：单次查询同时返回数据和总数.

        使用窗口函数在一次数据库往返中获取分页数据和总记录数，
        相比分开查询count和list可减少50%的数据库查询。

        Args:
            stage: 客户阶段筛选
            source_channel: 来源渠道筛选
            owner_id: 负责人ID筛选
            name: 姓名模糊搜索
            phone: 电话模糊搜索
            skip: 跳过记录数
            limit: 返回记录数
            include_deleted: 是否包含已删除记录

        Returns:
            (leads列表, 总记录数) 元组
        """
        # 构建基础查询
        subquery = self.session.query(Lead, func.count().over().label("total_count"))

        # 应用过滤条件
        if not include_deleted:
            subquery = subquery.filter(Lead.is_deleted == False)

        if stage is not None:
            subquery = subquery.filter(Lead.stage == stage)
        if source_channel is not None:
            subquery = subquery.filter(Lead.source_channel == source_channel)
        if owner_id is not None:
            subquery = subquery.filter(Lead.owner_id == owner_id)
        if name is not None and name.strip():
            subquery = subquery.filter(Lead.name.like(f"%{name.strip()}%"))
        if phone is not None and phone.strip():
            subquery = subquery.filter(Lead.phone.like(f"%{phone.strip()}%"))

        # 应用排序和分页
        subquery = subquery.order_by(Lead.created_at.desc()).offset(skip).limit(limit)

        # 执行查询
        results = subquery.all()

        if not results:
            return [], 0

        # 提取leads和总数
        leads = [row[0] for row in results]
        total = int(results[0][1]) if results else 0

        return leads, total

    def get_high_quality_leads(
        self,
        owner_id: int | None = None,
        min_grade: QualityGrade = QualityGrade.A,
        skip: int = 0,
        limit: int = 100
    ) -> list[Lead]:
        """获取高质量客户列表（S/A级）.

        Args:
            owner_id: 负责人ID筛选（可选）
            min_grade: 最低质量等级（默认A级及以上）
            skip: 跳过记录数
            limit: 返回记录数

        Returns:
            高质量客户列表，按质量分数降序排列
        """
        # 定义等级优先级映射
        grade_priority = {
            QualityGrade.S: 5,
            QualityGrade.A: 4,
            QualityGrade.B: 3,
            QualityGrade.C: 2,
            QualityGrade.D: 1,
        }

        min_priority = grade_priority.get(min_grade, 4)  # 默认A级
        acceptable_grades = [
            grade for grade, priority in grade_priority.items()
            if priority >= min_priority
        ]

        query = (
            self.session.query(Lead)
            .filter(Lead.is_deleted == False)
            .filter(Lead.quality_grade.in_(acceptable_grades))
            .filter(Lead.stage.notin_([LeadStage.CLOSED_WON, LeadStage.CLOSED_LOST]))
        )

        if owner_id is not None:
            query = query.filter(Lead.owner_id == owner_id)

        return (
            query.order_by(Lead.quality_score.desc())
            .offset(skip)
            .limit(limit)
            .all()
        )

    def get_priority_followups(
        self,
        owner_id: int,
        days_since_update: int = 3,
        min_quality_score: int = 70,
        limit: int = 20
    ) -> list[Lead]:
        """获取今日优先跟进客户列表.

        筛选条件：
        1. 质量分数 >= min_quality_score（默认70分）
        2. 超过N天未更新（默认3天）
        3. 未成交/流失
        4. 按质量分数降序

        Args:
            owner_id: 负责人ID
            days_since_update: 未更新天数阈值（默认3天）
            min_quality_score: 最低质量分数（默认70分）
            limit: 返回记录数（默认20）

        Returns:
            优先跟进客户列表
        """
        cutoff_date = datetime.now() - timedelta(days=days_since_update)

        return (
            self.session.query(Lead)
            .filter(Lead.owner_id == owner_id)
            .filter(Lead.is_deleted == False)
            .filter(Lead.quality_score >= min_quality_score)
            .filter(Lead.updated_at <= cutoff_date)
            .filter(Lead.stage.notin_([LeadStage.CLOSED_WON, LeadStage.CLOSED_LOST]))
            .order_by(Lead.quality_score.desc())
            .limit(limit)
            .all()
        )

    def get_quality_distribution(
        self,
        owner_id: int | None = None
    ) -> dict[str, int]:
        """获取客户质量等级分布统计.

        Args:
            owner_id: 负责人ID筛选（可选）

        Returns:
            质量等级分布字典，如 {"S": 5, "A": 20, "B": 35, "C": 30, "D": 10}
        """
        query = (
            self.session.query(
                Lead.quality_grade,
                func.count(Lead.id).label("count")
            )
            .filter(Lead.is_deleted == False)
            .filter(Lead.quality_grade.isnot(None))
            .group_by(Lead.quality_grade)
        )

        if owner_id is not None:
            query = query.filter(Lead.owner_id == owner_id)

        results = query.all()

        return {
            str(grade.value): count for grade, count in results
        }

    def filter_by_quality(
        self,
        quality_grades: list[QualityGrade] | None = None,
        min_quality_score: int | None = None,
        max_quality_score: int | None = None,
        owner_id: int | None = None,
        skip: int = 0,
        limit: int = 100
    ) -> tuple[list[Lead], int]:
        """按质量等级和分数筛选客户.

        Args:
            quality_grades: 质量等级列表
            min_quality_score: 最低质量分数
            max_quality_score: 最高质量分数
            owner_id: 负责人ID筛选
            skip: 跳过记录数
            limit: 返回记录数

        Returns:
            (客户列表, 总数) 元组
        """
        # 使用窗口函数优化查询
        subquery = self.session.query(
            Lead,
            func.count().over().label("total_count")
        ).filter(Lead.is_deleted == False)

        if quality_grades:
            subquery = subquery.filter(Lead.quality_grade.in_(quality_grades))

        if min_quality_score is not None:
            subquery = subquery.filter(Lead.quality_score >= min_quality_score)

        if max_quality_score is not None:
            subquery = subquery.filter(Lead.quality_score <= max_quality_score)

        if owner_id is not None:
            subquery = subquery.filter(Lead.owner_id == owner_id)

        # 按质量分数降序排列
        subquery = subquery.order_by(Lead.quality_score.desc()).offset(skip).limit(limit)

        results = subquery.all()

        if not results:
            return [], 0

        leads = [row[0] for row in results]
        total = int(results[0][1]) if results else 0

        return leads, total
